package com.gisuni.sddc.service;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;

import com.gisuni.sddc.dao.LandStatisticsDao;
import com.gisuni.sddc.model.Response;

@IocBean
public class CopyOfLandStatisticsService {

	private Log log = LogFactory.getLog(CopyOfLandStatisticsService.class);
	@Inject
	private LandStatisticsDao landStatisticsDao; 

	
	/**
	 * 统计制定时间段内各拓展组的登记情况
	 * 
	 * @param 
	 * @return
	 */
	public Response queryAndStatisticsLandByTeam(String departmentType,String projectType,String startdate,String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result=null;
		String selectstr="";
		try {
			
			selectstr = 
					" select tf.company,tf.department,tf.team,th.teamleader,th.pnum,'2' zhibiao,(NVL(th.pnum,0)/2)*100 wcd from     "+
							" (select tc.organization_name company,tb.organization_name department,tt.organization_name team ,tt.id teamid from  "+
							"          ORGANIZATION tt,                                                                                          "+
							"          ORGANIZATION tb,                                                                                          "+
							"          ORGANIZATION tc                                                                                           "+
							"    where tt.parent_id = tb.id                                                                                      "+
							"      and tb.parent_id = tc.id) tf,                                                                                 "+
							" (select tg.company,tg.department,tg.teamid,tg.team,tg.teamleader,count(tg.team) pnum from                          "+
							"    (select t.objectid,                                                                                             "+
							"          t.xh,                                                                                                     "+
							"          t.type,                                                                                                   "+
							"          t.registeruser,                                                                                           "+
							"          t1.displayname,                                                                                           "+
							"          tz.organization_name team,                                                                                "+
							"          tz.id teamid,                                                                                             "+
							"          (select tu.displayname                                                                                    "+
							"             from USERINFO tu                                                                                       "+
							"            where tu.userid = tz.organization_manager_id) teamleader,                                               "+
							"          tb.organization_name department,                                                                          "+
							"          tb.id departmentid,                                                                                       "+
							"          tc.organization_name company,                                                                             "+
							"          tc.id companyid                                                                                           "+
							"     from LANDEXPAND t,                                                                                             "+
							"          USER_ORGANIZATION t2,                                                                                     "+
							"          USERINFO t1,                                                                                              "+
							"          ORGANIZATION tz,                                                                                          "+
							"          ORGANIZATION tb,                                                                                          "+
							"          ORGANIZATION tc                                                                                           "+
							"    where t.registeruser = t2.user_id(+)                                                                            "+
							"      and t.registeruser = t1.userid(+)                                                                             "+
							"      and tz.id = t2.organization_develop_id                                                                        "+
							"      and tb.id = t2.organization_business_id                                                                       "+
							"      and tc.id = t2.organization_company_id                                                                        "+
							"      and t.type = '"+projectType+"'                                         "+
							"      and t.registerdate >= to_date('"+startdate+"', 'yyyy-mm-dd')           "+
							"      and t.registerdate <= to_date('"+enddate+"', 'yyyy-mm-dd')) tg         "+
							"      group by tg.teamid,tg.team,tg.teamleader,tg.department,tg.company) th                                         "+
							"      where tf.teamid = th.teamid(+) order by wcd desc                                                              ";
			Result= landStatisticsDao.queryAndStatisticsLandByTeam(selectstr);

			if (Result==null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}
	/**
	 * 统计制定时间段内各事业部的登记情况
	 * 
	 * @param 
	 * @return
	 */
	public Response queryAndStatisticsLandByDepartment(String departmentType,String projectType,String startdate,String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result=null;
		String selectstr="";
		try {
			
			selectstr = 
					"  select tf.company,tf.department,tf.gnum, tt.pnum,tf.gnum*2 zhibiao,ROUND((NVL(tt.pnum,0)/(tf.gnum*2))*100,1) wcd  from    "+
							" (select t2.organization_name company, t1.organization_name department,t1.id departmentid,tb.gnum                                       "+
							"                  from ORGANIZATION t1,                                                                                                 "+
							"                       ORGANIZATION t2,                                                                                                 "+
							"                       (select t3.parent_id, count(t3.parent_id) gnum                                                                   "+
							"                          from ORGANIZATION t3                                                                                          "+
							"                         where t3.organization_type = '3'                                                                               "+
							"                         group by t3.parent_id) tb                                                                                      "+
							"                 where t1.id = tb.parent_id                                                                                             "+
							"                   and t2.id = t1.parent_id                                                                                             "+
							"                   and t1.organization_type = '2')tf,                                                                                   "+
							" (select tg.company,tg.department,tg.departmentid,count(tg.department) pnum from                                                        "+
							"    (select t.objectid,                                                                                                                 "+
							"          t.xh,                                                                                                                         "+
							"          t.type,                                                                                                                       "+
							"          t.registeruser,                                                                                                               "+
							"          t1.displayname,                                                                                                               "+
							"          tb.organization_name department,                                                                                              "+
							"          tb.id departmentid,                                                                                                           "+
							"          tc.organization_name company                                                                                                  "+
							"     from LANDEXPAND t,                                                                                                                 "+
							"          USER_ORGANIZATION t2,                                                                                                         "+
							"          USERINFO t1,                                                                                                                  "+
							"          ORGANIZATION tz,                                                                                                              "+
							"          ORGANIZATION tb,                                                                                                              "+
							"          ORGANIZATION tc                                                                                                               "+
							"    where t.registeruser = t2.user_id(+)                                                                                                "+
							"      and t.registeruser = t1.userid(+)                                                                                                 "+
							"      and tz.id = t2.organization_develop_id                                                                                            "+
							"      and tb.id = t2.organization_business_id                                                                                           "+
							"      and tc.id = t2.organization_company_id                                                                                            "+
							"      and t.type = '"+projectType+"'                                         "+
							"      and t.registerdate >= to_date('"+startdate+"', 'yyyy-mm-dd')           "+
							"      and t.registerdate <= to_date('"+enddate+"', 'yyyy-mm-dd')) tg                                                                     "+
							"      group by tg.department,tg.departmentid,tg.company) tt                                                                             "+
							"      where tf.departmentid = tt.departmentid(+) order by wcd desc                                                                     ";
							 
			Result= landStatisticsDao.queryAndStatisticsLandByTeam(selectstr);

			if (Result==null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}
	
	/**
	 * 统计制定时间段内各集团公司的登记情况
	 * 
	 * @param 
	 * @return
	 */
	public Response queryAndStatisticsLandByCompany(String departmentType,String projectType,String startdate,String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result=null;
		String selectstr="";
		try {
			
			selectstr = 
					"  select tf.organization_name company,tf.gnum,th.pnum,tf.gnum*2 zhibiao, ROUND((NVL(th.pnum,0)/(tf.gnum*2))*100,1) wcd "+
							"  from (select t5.*, t4.organization_name                                                                                       "+
							"             from (select t.parent_id, sum(t.gnum) gnum                                                                         "+
							"                     from (select t1.id,                                                                                        "+
							"                                  t1.organization_name,                                                                         "+
							"                                  t1.parent_id,                                                                                 "+
							"                                  tb.gnum                                                                                       "+
							"                             from ORGANIZATION t1,                                                                              "+
							"                                  (select t3.parent_id,                                                                         "+
							"                                          count(t3.parent_id) gnum                                                              "+
							"                                     from ORGANIZATION t3                                                                       "+
							"                                    where t3.organization_type = '3'                                                            "+
							"                                    group by t3.parent_id) tb                                                                   "+
							"                            where t1.id = tb.parent_id                                                                          "+
							"                              and t1.organization_type = '2') t                                                                 "+
							"                    group by t.parent_id) t5,                                                                                   "+
							"                  ORGANIZATION t4                                                                                               "+
							"            where t4.id = t5.parent_id) tf,                                                                                     "+
							"  (select tg.companyid,tg.company,count(tg.company) pnum from                                                                   "+
							"    (select tc.id companyid,                                                                                                    "+
							"    tc.organization_name company                                                                                                "+
							"     from LANDEXPAND t,                                                                                                         "+
							"          USER_ORGANIZATION t2,                                                                                                 "+
							"          USERINFO t1,                                                                                                          "+
							"          ORGANIZATION tc                                                                                                       "+
							"    where t.registeruser = t2.user_id(+)                                                                                        "+
							"      and t.registeruser = t1.userid(+)                                                                                         "+
							"      and tc.id = t2.organization_company_id                                                                                    "+
							"      and t.type = '"+projectType+"'                                         "+
							"      and t.registerdate >= to_date('"+startdate+"', 'yyyy-mm-dd')           "+
							"      and t.registerdate <= to_date('"+enddate+"', 'yyyy-mm-dd')) tg                                                            "+
							"      group by tg.companyid,tg.company) th                                                                                      "+
							"      where tf.parent_id = th.companyid(+) order by wcd desc                                                                                     ";
			Result= landStatisticsDao.queryAndStatisticsLandByTeam(selectstr);

			if (Result==null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}
}
